var express 		= require('express');
var mysql   		= require('mysql');
var config  		= require('../config.json');
var sqlconfig 		= config.mysql;
var router  		= express.Router();
var pool    		= mysql.createPool(sqlconfig);

/*
  Name: data
  Args:
	data:data to be return
	res :response to write data
  Return : 
	Valid String for Client to use
  Description：
	  Return the valid  Data String to client if there is no error
*/

function data(data,res)
{
	console.log("LOG:RESPONSE:"+JSON.stringify(data));
	return res.end(JSON.stringify({err:0,response:data}));
}

/*
  Name: error
  Args:
	res:response to write err
	err:error message to return
	errcode: description of an error
  Return : 
	Valid String for Client to use
  Description：
	Return the error message to the client 
*/

function error(err,errcode,res)
{
	console.log("LOG:ERROR:"+JSON.stringify(errcode));
	return res.end(JSON.stringify({err : err,errcode : errcode}));
}

/*
  Name: BeforeAspect 
  Args: 
	request:Object
  Return: 
	data return from this aspect
  Description:
	preprocessor
*/

function BeforeAspect(request)
{
	
	console.log("LOG:REQUEST:"+JSON.stringify(request));
	return request;
}

/*
  Name:Student_Get_All[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	get all score
*/

function Student_Get_All(req,res,next)
{
	var query = "select s.*,subject_name FROM ( SELECT * FROM score  WHERE stu_id = ?) s LEFT JOIN `subject` on s.subject_id = `subject`.subject_id ORDER BY score_year DESC;"
	try{
		var args = BeforeAspect(req.body);
		
		if(typeof(req.session.user) !=  'object' )
		{
			error(402,"Login First",res);
			return;
		}

		if(req.session.user_type != 'PARENT' && req.session.user_type != 'STUDENT')
		{
			error(403,"Permission Denied",res);
			return;
		}

		var query_args = [];
		query_args.push(req.session.user['stu_id']);
		
		pool.query(query,query_args,
		function(err,rows,fileds)
		{
			if(err)
			{
				error(300,err,res);
				return;
			}
			else
			{
				data(rows,res);
			}
			return;
		});
	}
	catch(err)
	{
		error( -1 ,err,res);
	}
	return;
}

/*
  Name:Student_Get_Scope_Subject[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
			subject_id: subject_id to scope
			subject_name[Optional]:subject_name to scope
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	get score with scope subject
*/

function Student_Get_Scope_Subject(req,res,next)
{
	var query = "SELECT s.*,subject_name FROM (SELECT * FROM score WHERE stu_id = ? ) s LEFT JOIN `subject` ON s.subject_id = `subject`.subject_id WHERE s.subject_id = ? ORDER BY score_year DESC; ";
	try{
		var args = BeforeAspect(req.body);
		
		if(typeof(req.session.user) !=  'object' )
		{
			error(402,"Login First",res);
			return;
		}

		if(req.session.user_type != 'PARENT' && req.session.user_type != 'STUDENT')
		{
			error(403,"Permission Denied",res);
			return;
		}

		var query_args = [];
		query_args.push(req.session.user['stu_id']);

		if(typeof(args['subject_id']) != 'number')
		{
			if(typeof(args['subject_name']) == 'string')
			{
				query = "SELECT s.*,subject_name FROM (SELECT * FROM score WHERE stu_id = ? ) hw LEFT JOIN `subject` ON hw.subject_id = `subject`.subject_id WHERE subject_name = ? ORDER BY score_year DESC; ";
				query_args.push(args['subject_name']);
			}
			else
			{
				error(401,"Invalid Argument",res);
				return;
			}
			
		}
		else
		{
			query_args.push(args['subject_id']);
		}

		pool.query(query,query_args,
		function(err,rows,fileds)
		{
			if(err)
			{
				error(500,err,res);
				return;
			}
			else
			{
				data(rows,res);
			}
			return;
		});
	}
	catch(err)
	{
		error( -1 ,err,res);
	}
	return;
}

/*
  Name:Student_Get_Scope_score_year[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
			score_year_l : left_limit of score_year
			score_year_r : rgiht_limit of score_year
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	get scope with scope 
*/

function Student_Get_Scope_score_year(req,res,next)
{
	var query = "SELECT s.*,subject_name FROM (SELECT * FROM score WHERE stu_id = ? ) s LEFT JOIN `subject` ON s.subject_id = `subject`.subject_id WHERE score_year BETWEEN ? AND ? ORDER BY score_year DESC; ";
	try{
		var args = BeforeAspect(req.body);
		
		if(typeof(req.session.user) !=  'object' )
		{
			error(402,"Login First",res);
			return;
		}

		if(req.session.user_type != 'PARENT' && req.session.user_type != 'STUDENT')
		{
			error(403,"Permission Denied",res);
			return;
		}

		var query_args = [];
		query_args.push(req.session.user['class_id']);

		if(typeof(args['score_year_l']) == 'undefined' || typeof(args['score_year_r']) == 'undefined')
		{
			error(401,"Invalid Argument",res);
			return;				
		}
		else
		{
			query_args.push(args['score_year_l']);
			query_args.push(args['score_year_r']);
		}

		pool.query(query,query_args,
		function(err,rows,fileds)
		{
			if(err)
			{
				error(500,err,res);
				return;
			}
			else
			{
				data(rows,res);
			}
			return;
		});
	}
	catch(err)
	{
		error( -1 ,err,res);
	}
	return;
}

/*
  Name:Student_Get_Scope_hw_deadline[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
			hw_deadline_l : left limit of hw_deadline
			hw_deadline_r : right limit og hw_deadline
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	get homework with scope hw_deadline
*/
//	
//	function Student_Get_Scope_hw_deadline(req,res,next)
//	{
//		var query = "SELECT hw.*,subject_name FROM (SELECT * FROM homework WHERE class_id = ? ) hw LEFT JOIN `subject` ON hw.subject_id = `subject`.subject_id WHERE hw_deadline BETWEEN ? AND ? ORDER BY hw_time DESC; ";
//		try{
//			var args = BeforeAspect(req.body);
//			
//			if(typeof(req.session.user) !=  'object' )
//			{
//				error(402,"Login First",res);
//				return;
//			}
//	
//			if(req.session.user_type != 'PARENT' && req.session.user_type != 'STUDENT')
//			{
//				error(403,"Permission Denied",res);
//				return;
//			}
//	
//			var query_args = [];
//			query_args.push(req.session.user['class_id']);
//	
//			if(typeof(args['hw_deadline_l']) == 'undefined' || typeof(args['hw_deadline_r']) == 'undefined')
//			{
//				error(401,"Invalid Argument",res);
//				return;				
//			}
//			else
//			{
//				query_args.push(args['hw_deadline_l']);
//				query_args.push(args['hw_deadline_r']);
//			}
//	
//			pool.query(query,query_args,
//			function(err,rows,fileds)
//			{
//				if(err)
//				{
//					error(500,err,res);
//					return;
//				}
//				else
//				{
//					data(rows,res);
//				}
//				return;
//			});
//		}
//		catch(err)
//		{
//			error( -1 ,err,res);
//		}
//		return;
//	}
//	

/*
  Name:Teacher_Get_All[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
			stu_id : sudent id to get score
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	get homework of a class
*/

function Teacher_Get_All(req,res,next)
{
	var query = "SELECT s.*,subject_name FROM (SELECT * FROM score WHERE stu_id = ? ) s LEFT JOIN `subject` ON s.subject_id = `subject`.subject_id ORDER BY score_year DESC; ";
	try{
		var args = BeforeAspect(req.body);
		
		if(typeof(req.session.user) !=  'object' )
		{
			error(402,"Login First",res);
			return;
		}

		if(req.session.user_type != 'TEACHER')
		{
			error(403,"Permission Denied",res);
			return;
		}

		var query_args = [];
		
		if(typeof(args['stu_id']) == 'undefined')
		{
			error(401,"Invalid Argument",res);
			return;				
		}
		else
		{
			query_args.push(args['stu_id']);
		}

		pool.query(query,query_args,
		function(err,rows,fileds)
		{
			if(err)
			{
				error(500,err,res);
				return;
			}
			else
			{
				data(rows,res);
			}
			return;
		});
	}
	catch(err)
	{
		error( -1 ,err,res);
	}
	return;
}

/*
  Name:Teacher_Get_Scope_Subject[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
			stu_id		: student_id to get score	
			subject_id : subject_id to scope score
			subject_name [Optional] : subject_name to scope score
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	get score of a student with subject scope
*/

function Teacher_Get_Scope_Subject(req,res,next)
{
	var query = "SELECT s.*,subject_name FROM (SELECT * FROM score WHERE stu_id = ? ) s LEFT JOIN `subject` ON s.subject_id = `subject`.subject_id WHERE s.subject_id = ? ORDER BY score_year DESC; ";
	try{
		var args = BeforeAspect(req.body);
		
		if(typeof(req.session.user) !=  'object' )
		{
			error(402,"Login First",res);
			return;
		}

		if(req.session.user_type != 'TEACHER')
		{
			error(403,"Permission Denied",res);
			return;
		}

		var query_args = [];

		if(typeof(args['stu_id']) != 'number')
		{
			error(401,"Invalid Argument",res);
			return;
		}
		else
		{
			query_args.push(args['stu_id']);
		}

		if(typeof(args['subject_id']) != 'number')
		{
			
			if(typeof(args['subject_name']) == 'string')
			{
				query = "SELECT s.*,subject_name FROM (SELECT * FROM score WHERE stu_id = ? ) s LEFT JOIN `subject` ON s.subject_id = `subject`.subject_id WHERE subject_name = ? ORDER BY score_year DESC; ";
				query_args.push(args['subject_name']);
			}
			else
			{
				error(401,"Invalid Argument",res);
				return;
			}							
		}
		else
		{
			query_args.push(args['subject_id']);
		}

		pool.query(query,query_args,
		function(err,rows,fileds)
		{
			if(err)
			{
				error(500,err,res);
				return;
			}
			else
			{
				data(rows,res);
			}
			return;
		});
	}
	catch(err)
	{
		error( -1 ,err,res);
	}
	return;
}

/*
  Name:Teacher_Get_Scope_score_year[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
			stu_id : stu_id to get score
			score_year_l : left limit of score_year
			score_year_r : right limit of score_year
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	get score of a student with score_year scope
*/

function Teacher_Get_Scope_score_year(req,res,next)
{
	var query = "SELECT s.*,subject_name FROM (SELECT * FROM score WHERE stu_id = ? ) s LEFT JOIN `subject` ON s.subject_id = `subject`.subject_id WHERE score_year BETWEEN ? AND ? ORDER BY score_year DESC; ";
	try{
		var args = BeforeAspect(req.body);
		
		if(typeof(req.session.user) !=  'object' )
		{
			error(402,"Login First",res);
			return;
		}

		if(req.session.user_type != 'TEACHER')
		{
			error(403,"Permission Denied",res);
			return;
		}

		var query_args = [];

		if(typeof(args['stu_id']) != 'number' 
			|| typeof(args['score_year_l']) != 'number' 
			|| typeof(args['score_year_r']) != 'number'
			)
		{
			error(401,"Invalid Argument",res);
			return;		
		}
		else
		{
			query_args.push(args['stu_id']);
			query_args.push(args['score_year_l']);
			query_args.push(args['score_year_r']);
		}

		pool.query(query,query_args,
		function(err,rows,fileds)
		{
			if(err)
			{
				error(500,err,res);
				return;
			}
			else
			{
				data(rows,res);
			}
			return;
		});
	}
	catch(err)
	{
		error( -1 ,err,res);
	}
	return;
}

//	/*
//	  Name:Teacher_Get_Scope_hw_deadline[I] 
//	  Args: 
//		req:
//			JSON :[ args] arguments of this query
//				class_id : class to get homework
//				hw_deadline_l : left limit of hw_deadline
//				hw_deadline_r : right limit of hw_deadline
//		res: response to write data/error
//		next:
//	  Return: 
//	  	None
//	  Description:
//		get homework of a class with hw_deadline scope
//	*/
//	
//	function Teacher_Get_Scope_hw_deadline(req,res,next)
//	{
//		var query = "SELECT hw.*,subject_name FROM (SELECT * FROM homework WHERE class_id = ? ) hw LEFT JOIN `subject` ON hw.subject_id = `subject`.subject_id WHERE hw_deadline BETWEEN ? AND ? ORDER BY hw_time DESC; ";
//		try{
//			var args = BeforeAspect(req.body);
//			
//			if(typeof(req.session.user) !=  'object' )
//			{
//				error(402,"Login First",res);
//				return;
//			}
//	
//			if(req.session.user_type != 'TEACHER')
//			{
//				error(403,"Permission Denied",res);
//				return;
//			}
//	
//			var query_args = [];
//	
//			if(typeof(args['class_id']) != 'number' || typeof(args['hw_deadline_l']) != 'string' || typeof(args['hw_deadline_r']) != 'string')
//			{
//				error(401,"Invalid Argument",res);
//				return;		
//			}
//			else
//			{
//				query_args.push(args['class_id']);
//				query_args.push(args['hw_deadline_l']);
//				query_args.push(args['hw_deadline_r']);
//			}
//	
//			pool.query(query,query_args,
//			function(err,rows,fileds)
//			{
//				if(err)
//				{
//					error(500,err,res);
//					return;
//				}
//				else
//				{
//					data(rows,res);
//				}
//				return;
//			});
//		}
//		catch(err)
//		{
//			error( -1 ,err,res);
//		}
//		return;
//	}

/*
  Name:Teacher_Add_Score[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
			stu_id 		: student to add score
			subject_id 	: subject to add score to
			score 		: score
			score_semester	: score_semester
			score_year	: score_year
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	Add score info to a student 
*/

function Teacher_Add_Score(req,res,next)
{
	var query = "INSERT INTO score SET ?;";
	try{
		var args = BeforeAspect(req.body);
		
		if(typeof(req.session.user) !=  'object' )
		{
			error(402,"Login First",res);
			return;
		}

		if(req.session.user_type != 'TEACHER')
		{
			error(403,"Permission Denied",res);
			return;
		}

		var query_args = {};

		if(typeof(args['stu_id']) != 'number' 
			|| typeof(args['subject_id']) != 'number' 
			|| typeof(args['score']) != 'number' 
			|| typeof(args['score_semester']) != 'number' 
			|| typeof(args['score_year']) != 'number' 
			)
		{
			error(401,"Invalid Argument",res);
			return;		
		}
		else
		{
			query_args['stu_id']	= args['stu_id'];
			query_args['subject_id']= args['subject_id'];
			query_args['score']	= args['score'];
			query_args['score_semester']	= args['score_semester'];
			query_args['score_year']= args['score_year'];
		}

		pool.query(query,query_args,
		function(err,rows,fileds)
		{
			if(err)
			{
				error(500,err,res);
				return;
			}
			else
			{
				data(rows,res);
			}
			return;
		});
	}
	catch(err)
	{
		error( -1 ,err,res);
	}
	return;
}

/*
  Name:Teacher_Delete_Score[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
			score_id: hw_id of score entity to delete 
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	Delete score of a student
*/

function Teacher_Delete_Score(req,res,next)
{
	var query = "DELETE FROM score WHERE score_id = ?;";
	try{
		var args = BeforeAspect(req.body);
		
		if(typeof(req.session.user) !=  'object' )
		{
			error(402,"Login First",res);
			return;
		}

		if(req.session.user_type != 'TEACHER')
		{
			error(403,"Permission Denied",res);
			return;
		}

		var query_args = [];

		if(typeof(args['score_id']) != 'number' )
		{
			error(401,"Invalid Argument",res);
			return;		
		}
		else
		{
			query_args.push(args['score_id']);
		}

		pool.query(query,query_args,
		function(err,rows,fileds)
		{
			if(err)
			{
				error(500,err,res);
				return;
			}
			else
			{
				data(rows,res);
			}
			return;
		});
	}
	catch(err)
	{
		error( -1 ,err,res);
	}
	return;
}


/*
  Name:Teacher_Modify_Score[I] 
  Args: 
	req:
		JSON :[ args] arguments of this query
			score_id: score_id of score entity to delete 
			score: new score
	res: response to write data/error
	next:
  Return: 
  	None
  Description:
	Modify score 
*/

function Teacher_Modify_Score(req,res,next)
{
	var query = "UPDATE score SET score = ? WHERE score_id = ?;";
	try{
		var args = BeforeAspect(req.body);
		
		if(typeof(req.session.user) !=  'object' )
		{
			error(402,"Login First",res);
			return;
		}

		if(req.session.user_type != 'TEACHER')
		{
			error(403,"Permission Denied",res);
			return;
		}

		var query_args = [];

		if(typeof(args['score_id']) != 'number'
			|| typeof(args['score']) != 'number'

		 )
		{
			error(401,"Invalid Argument",res);
			return;		
		}
		else
		{
			query_args.push(args['score']);
			query_args.push(args['score_id']);
		}

		pool.query(query,query_args,
		function(err,rows,fileds)
		{
			if(err)
			{
				error(500,err,res);
				return;
			}
			else
			{
				data(rows,res);
			}
			return;
		});
	}
	catch(err)
	{
		error( -1 ,err,res);
	}
	return;
}

//Register '/' GET router
router.get('/', function(req, res, next) 
{
  data({},res);
});

//Register all routers 
var functions = {	 "/stu_all/"     	: Student_Get_All
			,"/stu_scope/subject"	: Student_Get_Scope_Subject
			,"/stu_scope/score_year": Student_Get_Scope_score_year
//		//	,"/stu_scope/hw_deadline" : Student_Get_Scope_hw_deadline
			,"/tea_all/"     	: Teacher_Get_All
			,"/tea_scope/subject"	: Teacher_Get_Scope_Subject
			,"/tea_scope/score_year" : Teacher_Get_Scope_score_year
//			,"/tea_scope/hw_deadline" : Teacher_Get_Scope_hw_deadline				
			,"/tea_add_score" 	: Teacher_Add_Score
			,"/tea_del_score" 	: Teacher_Delete_Score 
			,"/tea_modify_score" 	: Teacher_Modify_Score 
			}

for(var i in functions) 
{
	console.log("FUNCTION_LOADED(score):"+i);
	router.post(i, functions[i]);
}

module.exports = router;
